package com.yutel.system.service.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.yutel.system.repository.BusinessTableBean;
import com.yutel.system.repository.ColumnsBean;
import com.yutel.system.service.IDmlService;

/**
 * 数据管理操作服务，有改表名及改列名的操作只能用于MySQL
 * 
 */
public class MySqlDmlService extends JdbcDaoSupport implements IDmlService {

	private final String QUERY_TABLE_BY_KEYWORD = "SELECT TABLE_ID, TABLE_NAME, BUSINESS_NAME FROM XS_BUSINESS_TABLE WHERE SYSTEM_TABLE != '是' AND (BUSINESS_NAME LIKE ? OR TABLE_NAME LIKE ?)";
	private final String QUERY_TABLE_BY_ID = "SELECT TABLE_ID, TABLE_NAME, BUSINESS_NAME FROM XS_BUSINESS_TABLE WHERE TABLE_ID = ? ";
	private final String QUERY_TABLEID = "SELECT MAX(TABLE_ID) FROM XS_BUSINESS_TABLE";
	private final String INSERT_TABLE = "INSERT INTO XS_BUSINESS_TABLE(TABLE_ID,TABLE_NAME, BUSINESS_NAME, SYSTEM_TABLE) VALUES(?,?, ?, '否')";
	private final String UPDATE_TABLE = "UPDATE XS_BUSINESS_TABLE SET TABLE_NAME = ?, BUSINESS_NAME = ? WHERE TABLE_ID = ?";
	private final String DELETE_TABLE = "DELETE FROM XS_BUSINESS_TABLE WHERE TABLE_ID = ? ";
	private final String QUERY_COLUMN_BY_TABLE_ID = "SELECT COL_ID, COL_NAME, BUSINESS_NAME, DATA_TYPE, NULL_ABLE, IF_PRIMARY FROM XS_BUSINESS_COL WHERE COL_NAME != 'ID' AND TABLE_ID = ? ";
	private final String QUERY_COLUMNID = "SELECT MAX(COL_ID) FROM XS_BUSINESS_COL";
	private final String INSERT_COLUMN = "INSERT INTO XS_BUSINESS_COL(COL_ID,TABLE_ID, COL_NAME, BUSINESS_NAME, DATA_TYPE, NULL_ABLE, IF_PRIMARY) VALUES(?,?, ?, ?, ?, ?, ?)";
	private final String UPDATE_COLUMN = "UPDATE XS_BUSINESS_COL SET COL_NAME = ?, BUSINESS_NAME = ?, DATA_TYPE = ?, NULL_ABLE = ? WHERE COL_ID = ?";
	private final String DELETE_COLUMN_BY_ID = "DELETE FROM XS_BUSINESS_COL WHERE COL_ID = ?";
	private final String DELETE_COLUMN_BY_TABLE_ID = "DELETE FROM XS_BUSINESS_COL WHERE TABLE_ID = ?";

	public List<BusinessTableBean> queryTablesByKeyWord(String keyWord) {
		if (keyWord == null) {
			keyWord = "";
		}
		keyWord = "%" + keyWord + "%";
		List<BusinessTableBean> tables = getJdbcTemplate().query(
				QUERY_TABLE_BY_KEYWORD, new Object[] { keyWord, keyWord },
				new MySqlBusinessTableBeanRowMaper());
		for (BusinessTableBean table : tables) {
			getJdbcTemplate().query(QUERY_COLUMN_BY_TABLE_ID,
					new Object[] { table.getTableId() },
					new MySqlColumnsBeanResultSetExtractor(table));
		}
		return tables;
	}

	public void saveTable(BusinessTableBean table) {
		if (table.getTableId() == null) {
			createTable(table);
			createTableMetaData(table);
		} else {
			BusinessTableBean oldTable = queryTableById(table.getTableId());
			alterTable(oldTable, table);
			alterTableMetaData(oldTable, table);
		}
	}

	public void dropTable(String tableId) {
		BusinessTableBean table = queryTableById(tableId);
		getJdbcTemplate().update("DROP TABLE " + table.getTableName());
		getJdbcTemplate().update(DELETE_TABLE,
				new Object[] { table.getTableId() });
		getJdbcTemplate().update(DELETE_COLUMN_BY_TABLE_ID,
				new Object[] { table.getTableId() });
	}

	/**
	 * 创建表
	 * 
	 * @param table
	 */
	private void createTable(BusinessTableBean table) {
		String columns = "ID int PRIMARY KEY";
		for (ColumnsBean column : table.getColumns()) {
			columns += ",\n" + columnDefinition(column);
		}
		String createSql = "CREATE TABLE " + table.getTableName() + "("
				+ columns + ")";
		getJdbcTemplate().execute(createSql);
	}

	/**
	 * 创建表信息
	 * 
	 * @param table
	 */
	private void createTableMetaData(BusinessTableBean table) {
		String tableId = ""
				+ (getJdbcTemplate().queryForInt(QUERY_TABLEID) + 1);
		getJdbcTemplate().update(
				INSERT_TABLE,
				new Object[] { tableId, table.getTableName(),
						table.getTableBusinessName() });
		createColumnMetaData(tableId, ColumnsBean.KEY_COLUMN);
		for (ColumnsBean column : table.getColumns()) {
			createColumnMetaData(tableId, column);
		}
	}

	/**
	 * 创建列信息
	 * 
	 * @param tableId
	 * @param column
	 */
	private void createColumnMetaData(String tableId, ColumnsBean column) {
		String columnId = ""
				+ (getJdbcTemplate().queryForInt(QUERY_COLUMNID) + 1);
		getJdbcTemplate().update(
				INSERT_COLUMN,
				new Object[] { columnId, tableId, column.getFieldName(),
						column.getBusinName(), column.getFieldType(),
						column.getNullAble(), column.getPrimaryKey() });
	}

	/**
	 * 修改表定义
	 * 
	 * @param newTable
	 */
	private void alterTable(BusinessTableBean oldTable,
			BusinessTableBean newTable) {
		// 改表名
		if (!newTable.getTableName().equalsIgnoreCase(oldTable.getTableName())) {
			renameTable(oldTable, newTable);
		}
		// 将新表中不存在的列删除
		for (ColumnsBean oldColumn : oldTable.getColumns()) {
			boolean remain = false;
			for (ColumnsBean newColumn : newTable.getColumns()) {
				if (oldColumn.getColumnId().equalsIgnoreCase(
						newColumn.getColumnId())) {
					remain = true;
					break;
				}
			}
			if (!remain) {
				dropColumn(newTable, oldColumn);
			}
		}
		// 增加列，修改列，删除列
		for (ColumnsBean newColumn : newTable.getColumns()) {
			if (newColumn.getColumnId() == null) {
				addColumn(newTable, newColumn);
				continue;
			}
			for (ColumnsBean oldColumn : oldTable.getColumns()) {
				if (newColumn.getColumnId().equalsIgnoreCase(
						oldColumn.getColumnId())) {
					alterColumn(newTable, oldColumn, newColumn);
					break;
				}
			}
		}
	}

	/**
	 * 修改表信息
	 * 
	 * @param oldTable
	 * @param newTable
	 */
	private void alterTableMetaData(BusinessTableBean oldTable,
			BusinessTableBean newTable) {
		// 更改表信息
		if (!(newTable.getTableName().equalsIgnoreCase(oldTable.getTableName()) && newTable
				.getTableBusinessName().equals(oldTable.getTableBusinessName()))) {
			getJdbcTemplate().update(
					UPDATE_TABLE,
					new Object[] { newTable.getTableName(),
							newTable.getTableBusinessName(),
							newTable.getTableId() });
		}

		// 增加，修改列信息
		for (ColumnsBean newColumn : newTable.getColumns()) {
			if (newColumn.primaryKey()) {
				continue;
			}
			if (newColumn.getColumnId() == null) {
				createColumnMetaData(newTable.getTableId(), newColumn);
				continue;
			}
			for (ColumnsBean oldColumn : oldTable.getColumns()) {
				if (newColumn.getColumnId().equalsIgnoreCase(
						oldColumn.getColumnId())) {
					if (!(newColumn.getFieldName().equalsIgnoreCase(
							oldColumn.getFieldName())
							&& newColumn.getBusinName().equals(
									oldColumn.getBusinName())
							&& newColumn.getFieldType().equals(
									oldColumn.getFieldType()) && newColumn
							.getNullAble().equals(oldColumn.getNullAble()))) {
						getJdbcTemplate().update(
								UPDATE_COLUMN,
								new Object[] { newColumn.getFieldName(),
										newColumn.getBusinName(),
										newColumn.getFieldType(),
										newColumn.getNullAble(),
										newColumn.getColumnId() });
					}
					break;
				}
			}
		}
		// 将新表中不存在的列信息删除
		for (ColumnsBean oldColumn : oldTable.getColumns()) {
			if (oldColumn.primaryKey()) {
				continue;
			}
			boolean remain = false;
			for (ColumnsBean newColumn : newTable.getColumns()) {
				if (oldColumn.getColumnId().equalsIgnoreCase(
						newColumn.getColumnId())) {
					remain = true;
					break;
				}
			}
			if (!remain) {
				getJdbcTemplate().update(DELETE_COLUMN_BY_ID,
						new Object[] { oldColumn.getColumnId() });
			}
		}
	}

	public BusinessTableBean queryTableById(String tableId) {
		BusinessTableBean table = (BusinessTableBean) getJdbcTemplate()
				.queryForObject(QUERY_TABLE_BY_ID, new Object[] { tableId },
						new MySqlBusinessTableBeanRowMaper());
		getJdbcTemplate().query(QUERY_COLUMN_BY_TABLE_ID,
				new Object[] { tableId },
				new MySqlColumnsBeanResultSetExtractor(table));
		return table;
	}

	/**
	 * 重命名表
	 * 
	 * @param oldTable
	 * @param newTable
	 */
	private void renameTable(BusinessTableBean oldTable,
			BusinessTableBean newTable) {
		getJdbcTemplate().execute(
				"ALTER TABLE " + oldTable.getTableName() + " rename "
						+ newTable.getTableName());
	}

	/**
	 * 增加列
	 * 
	 * @param table
	 * @param column
	 */
	private void addColumn(BusinessTableBean table, ColumnsBean column) {
		getJdbcTemplate().execute(
				"ALTER TABLE " + table.getTableName() + " ADD "
						+ columnDefinition(column));
	}

	/**
	 * 删除列
	 * 
	 * @param table
	 * @param column
	 */
	private void dropColumn(BusinessTableBean table, ColumnsBean column) {
		getJdbcTemplate().execute(
				"ALTER TABLE " + table.getTableName() + " DROP COLUMN "
						+ column.getFieldName());
	}

	/**
	 * 更改列
	 * 
	 * @param table
	 * @param oldColumn
	 * @param newColumn
	 */
	private void alterColumn(BusinessTableBean table, ColumnsBean oldColumn,
			ColumnsBean newColumn) {
		if (newColumn.getFieldName().equalsIgnoreCase(oldColumn.getFieldName())
				|| newColumn.getDbType()
						.equalsIgnoreCase(oldColumn.getDbType())
				|| newColumn.getNullAble().equals(oldColumn.getNullAble())) {
			getJdbcTemplate().execute(
					"ALTER TABLE " + table.getTableName() + " CHANGE COLUMN "
							+ oldColumn.getFieldName() + " "
							+ columnDefinition(newColumn));
		}
	}

	private String columnDefinition(ColumnsBean column) {
		return column.getFieldName() + " " + column.getDbType() + " "
				+ (column.nullAble() ? "NULL" : "NOT NULL");
	}
}

class MySqlBusinessTableBeanRowMaper implements RowMapper {
	public Object mapRow(ResultSet rs, int rowIndex) throws SQLException {
		BusinessTableBean table = new BusinessTableBean();
		table.setTableId(rs.getString("TABLE_ID"));
		table.setTableName(rs.getString("TABLE_NAME"));
		table.setTableBusinessName(rs.getString("BUSINESS_NAME"));
		return table;
	}
}

class MySqlColumnsBeanResultSetExtractor implements ResultSetExtractor {
	private BusinessTableBean table;

	public MySqlColumnsBeanResultSetExtractor(BusinessTableBean table) {
		this.table = table;
	}

	public Object extractData(ResultSet rs) throws SQLException,
			DataAccessException {
		while (rs.next()) {
			ColumnsBean column = new ColumnsBean();
			column.setColumnId(rs.getString("COL_ID"));
			column.setFieldName(rs.getString("COL_NAME"));
			column.setBusinName(rs.getString("BUSINESS_NAME"));
			column.setFieldType(rs.getString("DATA_TYPE"));
			column.setNullAble(rs.getString("NULL_ABLE"));
			column.setPrimaryKey(rs.getString("IF_PRIMARY"));
			table.addColumn(column);
		}
		return table;
	}
}